package com.cyy.view.search

import cn.hutool.core.io.FileUtil
import cn.hutool.core.util.CharsetUtil
import cn.hutool.core.util.StrUtil
import cn.hutool.setting.Setting
import com.cyy.model.Conss
import com.cyy.model.GmodelModel
import com.cyy.model.SqlHistory
import com.cyy.model.SqlViewModel
import com.cyy.view.GenEvent
import com.jfinal.kit.Kv
import com.jfinal.plugin.activerecord.Db
import javafx.geometry.Orientation
import javafx.stage.FileChooser
import tornadofx.*
import tornadofx.controlsfx.listSelectionView
import java.io.File

class SearchLeft : View("My View") {
    val gm: GmodelModel by inject()
    val sqlvm: SqlViewModel by inject()

    // 需查询的列
    val slp = listProperty<String>(gm.columns)
    // 被排除的列
    val tlp = listProperty<String>(gm.exCols)
    // 用于渲染webview组件的模板文件
    val wbtmpl = sqlvm.wbtmpl
    // where每件
    val whereCon = stringProperty()
    // where中的列名
    val whereCol = stringProperty()
    // 查询语句
    val sql = stringProperty("")

    val limits = intProperty()
    val orderCol = stringProperty("")

    // where条件列名的值
    val whereValue = stringProperty("")
    val hasWhere = booleanProperty(false)
    val searchSql = stringProperty()
    val kv = Kv.create()
    override val root = vbox {
        prefWidth = 400.0
        titledpane("鼠标点击查询") {

            // 这种实现方式比较简洁
            listSelectionView(slp, tlp) {
                slp.clear()
                tlp.clear()
                prefHeight = 700.0
//            orientation = Orientation.VERTICAL
            }
            form {
                fieldset(labelPosition = Orientation.VERTICAL) {
                    field("选择模板文件") {
                        button("...") {
                            action {
                                val projectPath = gm.curProjectPath.value

                                val efset = arrayOf(FileChooser.ExtensionFilter("选择模板文件", "*.*"))

                                val fnset = chooseFile("选择模板文件", efset, FileChooserMode.Single) {
                                    // p初始目录为当前项目目录
                                    initialDirectory = File(projectPath)
                                }
                                if (fnset.isNotEmpty()) {
                                    wbtmpl.value = "${fnset.first()}"
                                }
                            }
                        }
                        textfield(wbtmpl) {
                            text = "${gm.curProjectPath.value}/doc/tmpl/view/index.html"
                        }
                        button("Search").action {
                            search()
                        }
                    }
                    hbox {
                        field("Limit") {
                            combobox(limits, listOf(20, 50, 100, 200))
                            textfield(limits) {
                                text = "10"
                            }
                        }
                        field("order by") {
                            combobox(orderCol, tlp)
                            textfield(orderCol) {
                                text = "id"
                            }
                        }
                    }
                    checkbox("Where", hasWhere)
                    field() {
                        enableWhen(hasWhere)
                        combobox(gm.column, tlp) {
                            selectionModel.selectedItemProperty().addListener { _, _, _ ->
                                (if (!selectionModel.selectedItem.isNullOrEmpty()) {
                                    whereCon.value = "where 1=1 and ${gm.column.value} ${whereCol.value} ${whereValue.value}"
                                })
                            }
                        }
                        combobox(whereCol, Conss.compareOp) {
                            selectionModel.selectedItemProperty().addListener { _, _, _ ->
                                (if (!selectionModel.selectedItem.isNullOrEmpty()) {
                                    whereCon.value = "where 1=1 and ${gm.column.value} ${whereCol.value} '${whereValue.value}'"
                                })
                            }
                        }
                        textfield(whereValue) {
                            text = "1"

                        }
                    }
                    field {
                        enableWhen(hasWhere)
                        textfield(whereCon) {
                            text = "where 1=1"
                        }
                    }
                }
                fieldset {
                    field("保存查寻条件") {
                        textfield(searchSql) {
                            text = "searchAll"
                        }
                    }
                }

                buttonbar {
                    button("Search").action {
                        search()
                    }

                    button("保存查寻条件").action {
                        //                        searchSql.bind(Observab)
                        println(sql.value)
                        saveSearch(sql.value)
                    }
                }
            }
        }
    }

    fun saveSearch(sql: String) {
        val sqlFile = "searchHistory.txt"
        val setting = Setting(FileUtil.touch(sqlFile), CharsetUtil.CHARSET_UTF_8, true)

        if (setting.containsKey(searchSql.value).not()) {
            setting.set(searchSql.value, sql)
            // 不保存查询结果，如果要保存查询结果，注释下行代码
            val kv1=kv.set("ret", null)
            FileUtil.appendUtf8String("\n${searchSql.value}=${sql} | ${gm.dbname.value} | ${gm.leftTable.value} | ${kv1}", sqlFile)
            val obj = SqlHistory(searchSql.value, sql, gm.dbname.value, gm.leftTable.value, kv.toString())
            sqlvm.sqlList.add(obj)
        } else {
            println("该名称已使用")
        }
    }

    fun search() {
        // val sql = "select ${selectedCol.value} from ${gm.leftTable.value}"
        var cols = StrUtil.join(",", tlp.value)
        if (cols.isNullOrBlank()) {
            cols = "*"
            tlp.value = gm.columns
        }
//                    println(cols)
        kv.set("cols", tlp.value).set("title", gm.leftTable.value)

//        val sql1="""select #(columns ?? "*") from #(table) #@where()"""
//        val sql1="""select #(columns ?? "*") from #(table) #(where) limit #(limits)"""
//        val kv1=Kv.by("columns",cols).set("table",gm.leftTable.value).set("limits",limits.value).set("where",whereCon.value)
//        val sql =gm.engine.value.getTemplateByString(sql1).renderToString(kv1)
//

        val orderBy = "order by ${orderCol.value}"
        sql.value = "select ${cols} from ${gm.leftTable.value} ${whereCon.value}  ${orderBy} limit ${limits.value}"
        runAsync {
            try {
                val ret = Db.use().find(sql.value)
                kv.set("ret", ret)
            } catch (e: Exception) {
                println("search failed : ${e}")
                fire(GenEvent("search failed : ${e}"))
            }
        } ui { success ->
            gm.tplStringOut.value = gm.engine.value.getTemplate(wbtmpl.value).renderToString(kv)
        }
    }

}